MySQL基础 | 您所在的位置:网站首页 › join in的用法及例句 › MySQL基础 |
Mysql 多表查询详解
目录 Mysql 多表查询详解 一.前言 二.下面以实例进行分析 三 注意事项 案例1、三张表使用left join连接 一.前言上篇讲到Mysql中关键字执行的顺序,只涉及了一张表;实际应用大部分情况下,查询语句都会涉及到多张表格 : 1.1 多表连接有哪些分类? 1.2 针对这些分类有哪些连接方法? 1.3 这些连接方法分别作用于哪些应用场景? 这篇针对这三个点通过实例来讲述,目的是穷尽所有的场景和所有的方法,并且对每个方法的使用做实例。 首先先列举本篇用到的分类(内连接,外连接,交叉连接)和连接方法(如下): A)内连接:join,inner join B)外连接:left join,left outer join,right join,right outer join,union C)交叉连接:cross join 二.下面以实例进行分析两张假设有两张表格A和B,把表格当作一个集合,那么表格中的记录就是集合中的一个元素。 两张表格如下: TableA:TableB: 2.1 内连接(只有一种场景) select * from tab_a,tab_b where ... 等价于 select * from tab_a inner join tab_b 推荐使用inner join这种写法 )inner join 或者join(等同于inner join) select a.*, b.* from tablea a inner join tableb b on a.id = b.id或 select a.*, b.* from tablea a join tableb b on a.id = b.id结果如下: 应用场景: 这种场景下得到的是满足某一条件的A,B内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接。 2.2 外连接(六种场景) 2.2.1 left join 或者left outer join(等同于left join) select a.*, b.* from tablea a left join tableb b on a.id = b.id或者 select a.*, b.* from tablea a left outer join tableb b on a.id = b.id表a 表b 结果如下,TableB中不存在的记录填充Null: 应用场景: 这种场景下得到的是A的所有数据,和满足某一条件的B的数据; 2.2.2 [ left join 或者left outer join(等同于left join) ] + [ where B.column is null ] select a.id aid,a.age,b.id bid,b.name from tablea a left join tableb b on a.id = b.id Where b.id is null表a 表b left join表a的数据全部显示,匹配表b的数据也显示,而b.id再次过滤掉 表b的id为空的。 结果如下: 应用场景: 这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据",然后得到的A剩余数据; 2.2.3 right join 或者right outer join(等同于right join) select a.id aid,a.age,b.id bid,b.name from tablea a right join tableb b on a.id = b.id表a 表b 结果如下,TableB中更不存在的记录填充Null: a right join b:表b的数据全部显示,表a匹配的数据显示,不匹配的为空 应用场景: 这种场景下得到的是B的所有数据,和满足某一条件的A的数据; 2.2.4 [ right join 或者right outer join(等同于right join) ] + [ where A.column is null ] select a.id aid,a.age,b.id bid,b.name from tablea a right join tableb b on a.id = b.id where a.id is null表a 表b 结果如下: 应用场景: 这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“,然后得到的B剩余数据; 2.2.5 full join (mysql不支持,但是可以用 left join union right join代替) select a.id aid,a.age,b.id bid,b.name from tablea a left join tableb b on a.id = b.id union select a.id aid,a.age,b.id bid,b.name from tablea a right join tableb b on a.id = b.id表a 表b union过后,重复的记录会合并(id为2,3,4的三条记录),所以结果如下: 应用场景: 这种场景下得到的是满足某一条件的公共记录,和独有的记录 2.2.6 full join + is null(mysql不支持,但是可以用 (left join + is null) union (right join+is null代替) select a.id aid,a.age,b.id bid,b.name from tablea a left join tableb b on a.id = b.id where b.id is null union select a.id aid,a.age,b.id bid,b.name from tablea a right join tableb b on a.id = b.id where a.id is null结果如下: 应用场景: 这种场景下得到的是A,B中不满足某一条件的记录之和 注:上面共有其中七(2^3-1)种应用场景,还有一种是全空白,那就是什么都不查,七种情形包含了实际应用所有可能的场景 2.3 交叉连接 (cross join) 2.3.1 实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join: select a.id aid,a.age,b.id bid,b.name from tablea a cross join tableb b2.3.2 还可以为cross join指定条件 (where): select a.id aid,a.age,b.id bid,b.name from tablea a cross join tableb b where a.id = b.id表a 表b 结果如下; 注:这种情况下实际上实现了内连接的效果 三 注意事项上面仍然存在遗漏,那就是mysql对sql语句的容错问题,即在sql语句不完全符合书写建议的情况,mysql会允许这种情况,尽可能地解释它: 3.1 一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where; 3.2 一般内连接都需要加上on限定条件,如上面场景2.1;如果不加会被解释为交叉连接; 3.3 如果连接表格使用的是逗号,会被解释为交叉连接; 注:sql标准中还有union join和natural inner join,mysql不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到 总结:总结了mysql所有连接方法,其中有一些是之前没有注意到的问题,平时开发也都不外乎这些。 案例1、三张表使用left join连接A表:姓名,学号,班级编号 B表:学号,成绩 C表:班级编号,班级名称 最后想显示为 姓名,学号,成绩,班级名称 A、B表用where a.学号=b.学号查出之后再和C表左连接sql语句如下: select a.姓名,a.学号, b.成绩, c.班级名称 from A表 a left join B表 b on a.学号=b.学号 left join C表 c on a.班级编号=c.班级编号网上有说:A表和B表连接形成临时表,再和C表连接。 这里有执行顺序,自己还未检验,先备份,有时间再来深究:mysql多个join在一句先后执行顺序_mysql join的执行顺序??_湖水是你的眼神的博客-CSDN博客 |
CopyRight 2018-2019 实验室设备网 版权所有 |